﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Helper
{
    /// <summary>
    /// Database helper base class
    /// </summary>
    public abstract class DbHelper : IDbHelper
    {
        /// <summary>
        /// static connection string
        /// </summary>
        protected static string connectionString;

        /// <summary>
        /// interface of connection
        /// </summary>
        protected DbConnection conn;

        /// <summary>
        /// interface of command
        /// </summary>
        protected DbCommand cmd;

        /// <summary>
        /// if it is in transaction then true, else false 
        /// </summary>
        protected bool transactionSupport = false;

        /// <summary>
        /// Initialize connection and command with the default connection string(ConnectionString property).
        /// </summary>
        protected void Initialize()
        {
            Initialize(ConnectionString);
        }

        /// <summary>
        /// initialize connection and command with specific connection string
        /// </summary>
        /// <param name="connectionString"></param>
        protected abstract void Initialize(string connectionString);


        /// <summary>
        /// Initialize connection and command by transaction
        /// </summary>
        /// <param name="transaction">transaction</param>
        protected void Initialize(DbTransaction transaction)
        {
            if (transaction == null)
                throw new System.ArgumentNullException("transaction", "Parameter can not null");

            transactionSupport = true;
            conn = transaction.Connection;
            cmd = conn.CreateCommand();
            cmd.Transaction = transaction;
        }


        /// <summary>
        /// Get DataAdapter for current connection
        /// </summary>
        /// <returns></returns>
        protected abstract DbDataAdapter GetDataAdapter();


        #region constructor
        /// <summary>
        /// Constructor for default.
        /// </summary>
        protected DbHelper()
        {
            Initialize();
        }

        /// <summary>
        /// use specific connection string to initialize Database helper.
        /// </summary>
        protected DbHelper(string connectionString)
        {
            Initialize(connectionString);
        }

        /// <summary>
        /// Constructor for support the transaction.
        /// </summary>
        /// <param name="transaction">current transaction</param>
        protected DbHelper(DbTransaction transaction)
        {
            Initialize(transaction);
        }
        #endregion

        /// <summary>
        /// connection string
        /// </summary>
        public static string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        #region IDbHelper Members

        #region Open and Close Connection
        /// <summary>
        /// Open connection if it is not open
        /// </summary>
        public void OpenConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        /// <summary>
        /// Close the Connection for command
        /// </summary>
        public void CloseConnection()
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
        #endregion

        #region AddParameter
        /// <summary>
        /// Add the parameter
        /// </summary>
        /// <param name="paramName">Paramter name</param>
        public virtual void AddParameter(string paramName)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = paramName;

            cmd.Parameters.Add(param);
        }

        /// <summary>
        /// Add DateTime parameter
        /// </summary>
        /// <param name="paramName">Paramter name</param>
        /// <param name="paramValue">Parameter value,its type is DateTime</param>
        public virtual void AddParameter(string paramName, DateTime paramValue)
        {
            if (paramValue == DateTime.MinValue
                || paramValue == DateTime.MaxValue)
                AddParameter(paramName, DBNull.Value, DbType.DateTime);
            else
                AddParameter(paramName, paramValue, DbType.DateTime);
        }

        /// <summary>
        /// Add DateTime parameter
        /// </summary>
        /// <param name="paramName">Paramter name</param>
        /// <param name="paramValue">Parameter value,its type is DateTime</param>
        public virtual void AddParameter(string paramName, DateTime? paramValue)
        {
            if (paramValue.HasValue)
            {
                AddParameter(paramName, paramValue, DbType.DateTime);
            }
            else
            {
                AddParameter(paramName, DBNull.Value, DbType.DateTime);
            }
        }

        /// <summary>
        /// Add the parameter for sqlcommand
        /// </summary>
        /// <param name="paramName">Paramter name</param>
        /// <param name="paramValue">Parameter value,its type is Object</param>		
        public virtual void AddParameter(string paramName, object paramValue)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.Direction = ParameterDirection.Input;
            param.ParameterName = paramName;
            if (paramValue == null || paramValue == DBNull.Value)
                param.Value = DBNull.Value;
            else
                param.Value = paramValue;

            cmd.Parameters.Add(param);
        }

        /// <summary>
        /// Add the parameter for sqlcommand
        /// </summary>
        /// <param name="paramName">Paramter name</param>
        /// <param name="dbType">SqlDbType</param>
        /// <param name="paramValue">Parameter value,its type is Object</param>        
        public virtual void AddParameter(string paramName, object paramValue, DbType dbType)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.Direction = ParameterDirection.Input;
            param.ParameterName = paramName;
            param.DbType = dbType;
            param.Value = paramValue;

            cmd.Parameters.Add(param);
        }


        /// <summary>
        /// Add the return type parameter for sqlcommand
        /// </summary>
        /// <param name="paramName">Parameter name</param>
        public virtual void AddReturnParameter(string paramName)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.Direction = ParameterDirection.ReturnValue;
            param.ParameterName = paramName;
            param.DbType = DbType.Object;
            cmd.Parameters.Add(param);
        }

        /// <summary>
        /// Add output parameter for sqlcommand.
        /// default DbType is string, size is 100;
        /// </summary>
        /// <param name="paramName">Parameter name</param>                
        public virtual void AddOutputParameter(string paramName)
        {
            AddOutputParameter(paramName, DbType.String, 100);
        }

        /// <summary>
        /// Add output parameter for sqlcommand.
        /// if parameter type is char or varchar , size is required
        /// </summary>
        /// <param name="paramName">Parameter name</param>
        /// <param name="paramType">DbType</param>
        public virtual void AddOutputParameter(string paramName, DbType paramType)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.Direction = ParameterDirection.Output;
            param.ParameterName = paramName;
            param.DbType = paramType;

            cmd.Parameters.Add(param);
        }

        /// <summary>
        /// Add output parameter for sqlcommand
        /// </summary>
        /// <param name="paramName">Parameter name</param>
        /// <param name="paramType">Parameter type</param>
        /// <param name="size">if parameter type is char or varchar , size is required</param>
        public virtual void AddOutputParameter(string paramName, DbType paramType, int size)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.Direction = ParameterDirection.Output;
            param.ParameterName = paramName;
            param.DbType = paramType;
            if (size > 0) { param.Size = size; }

            cmd.Parameters.Add(param);
        }

        #endregion

        #region Set Parameter Value

        /// <summary>
        /// Set parameter value
        /// </summary>
        /// <param name="paramName">parameter name</param>
        /// <param name="paramValue">value</param>
        public virtual void SetParameterValue(string paramName, DateTime paramValue)
        {
            IDataParameter param = cmd.Parameters[paramName] as IDataParameter;
            if (paramValue == DateTime.MinValue || paramValue == DateTime.MaxValue)
                param.Value = DBNull.Value;
            else
                param.Value = paramValue;
        }

        /// <summary>
        /// Set parameter value
        /// </summary>
        /// <param name="paramName">parameter name</param>
        /// <param name="paramValue">value</param>
        public virtual void SetParameterValue(string paramName, DateTime? paramValue)
        {
            IDataParameter param = cmd.Parameters[paramName] as IDataParameter;
            if (paramValue.HasValue)
            {
                param.Value = paramValue.Value;
            }
            else
            {
                param.Value = DBNull.Value;
            }
        }

        /// <summary>
        /// Set parameter value
        /// </summary>
        /// <param name="paramName">parameter name</param>
        /// <param name="paramValue">value</param>
        public virtual void SetParameterValue(string paramName, object paramValue)
        {
            IDataParameter param = cmd.Parameters[paramName] as IDataParameter;
            if (paramValue == null)
                param.Value = DBNull.Value;
            else
                param.Value = paramValue;
        }

        #endregion

        #region Clear Parameter And Get Parameter Value
        /// <summary>
        /// Clear the parameter for sqlcommand
        /// </summary>		
        public void ClearParameter()
        {
            cmd.Parameters.Clear();
        }

        /// <summary>
        /// Get the parameter value for the specified parameter
        /// </summary>
        /// <param name="paramName">Parameter Name</param>
        /// <returns>Parameter Value</returns>
        public object GetParameterValue(string paramName)
        {
            if (cmd.Parameters[paramName] != null)
            {
                IDbDataParameter param = cmd.Parameters[paramName] as IDbDataParameter;
                return param.Value;
            }
            else
                return null;
        }

        #endregion

        #region Execute Command
        /// <summary>
        /// Encapluated the SqlCommand.ExecuteReader() method
        /// default command type is procedure
        /// </summary>
        /// <param name="commandText">procedure name</param>
        /// <returns>DataReader</returns>
        public IDataReader ExecuteDataReader(string commandText)
        {
            return ExecuteDataReader(commandText, true);
        }

        /// <summary>
        /// Encapluated the SqlCommand.ExecuteReader() method
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>		
        /// <param name="isProcedure">determine if the command text is stored procedure</param>
        /// <returns>DbDataReader</returns>        
        public IDataReader ExecuteDataReader(string commandText, bool isProcedure)
        {
            cmd.CommandType = (isProcedure ? CommandType.StoredProcedure : CommandType.Text);
            cmd.CommandText = commandText;

            try
            {
                OpenConnection();
                if (transactionSupport)
                {
                    return cmd.ExecuteReader();
                }
                else
                {
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception er)
            {
                CloseConnection();
                throw er;
            }
        }

        /// <summary>
        /// Encapluated the DbCommand.ExecuteScalar() method
        /// default command type is procedure
        /// </summary>
        /// <param name="commandText">procedure name</param>
        /// <returns>the first column value of the first row for the Executed Result Command</returns>
        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, true);
        }

        /// <summary>
        /// Encapluated the DbCommand.ExecuteScalar() method
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>		
        /// <param name="isProcedure">determine if the command text is stored procedure</param>	
        /// <returns>the first column value of the first row for the Executed Result Command</returns>		
        public object ExecuteScalar(string commandText, bool isProcedure)
        {
            cmd.CommandType = (isProcedure ? CommandType.StoredProcedure : CommandType.Text);
            cmd.CommandText = commandText;

            object obj = null;
            try
            {
                OpenConnection();
                obj = cmd.ExecuteScalar();
            }
            catch (Exception er)
            {
                CloseConnection();
                throw er;
            }
            finally
            {
                if (!transactionSupport)
                {
                    CloseConnection();
                }

            }

            return obj;
        }

        /// <summary>
        /// ExecuteNonQuery
        /// default command type is procedure
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>
        /// <returns>the count of effected Lines</returns>
        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, true);
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>		
        /// <param name="isProcedure">determine if the command text is stored procedure</param>	
        /// <returns>the count of effected Lines</returns>		
        public int ExecuteNonQuery(string commandText, bool isProcedure)
        {
            return ExecuteNonQuery(commandText, isProcedure, true);
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>		
        /// <param name="isProcedure">determine if the command text is stored procedure</param>	
        /// <param name="closeConnection">determine if colse connection after execute sql command</param>
        /// <returns>the count of effected Lines</returns>	
        public int ExecuteNonQuery(string commandText, bool isProcedure, bool closeConnection)
        {
            cmd.CommandType = (isProcedure ? CommandType.StoredProcedure : CommandType.Text);
            cmd.CommandText = commandText;

            int effectedline = 0;

            try
            {
                OpenConnection();
                effectedline = cmd.ExecuteNonQuery();
            }
            catch (Exception er)
            {
                CloseConnection();
                throw er;
            }
            finally
            {
                if (!transactionSupport)
                {
                    if (closeConnection)
                        CloseConnection();
                }

            }

            return effectedline;
        }

        /// <summary>
        /// Execute a command and return a DataSet
        /// default command type is procedure
        /// </summary>
        /// <param name="commandText">stored procedure name</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            return ExecuteDataSet(commandText, true);
        }

        /// <summary>
        /// Execute a command, and return a DataSet
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>		
        /// <param name="isProcedure">determine if the command text is stored procedure</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText, bool isProcedure)
        {
            cmd.CommandType = (isProcedure ? CommandType.StoredProcedure : CommandType.Text);
            cmd.CommandText = commandText;

            DbDataAdapter da = GetDataAdapter();
            da.SelectCommand = cmd;
            DataSet dsReturnData = new DataSet();

            try
            {
                OpenConnection();
                da.Fill(dsReturnData);
                return dsReturnData;
            }
            catch (Exception er)
            {
                CloseConnection();
                throw er;
            }
            finally
            {
                if (!transactionSupport)
                {
                    CloseConnection();
                }

            }
        }

        /// <summary>
        /// Execute a sql command and return a data table
        /// default command type is procedure
        /// </summary>
        /// <param name="commandText">procedure name</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string commandText)
        {
            return ExecuteDataTable(commandText, true);
        }

        /// <summary>
        /// Execute a sql command and return a data table
        /// </summary>
        /// <param name="commandText">command text or stored procedure</param>
        /// <param name="isProcedure">determine if the command text is stored procedure</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string commandText, bool isProcedure)
        {
            return ExecuteDataTable(commandText, isProcedure, 0, 0);
        }

        public DataTable ExecuteDataTable(string commandText, int pageIndex, int pageSize)
        {
            return ExecuteDataTable(commandText, true, pageIndex, pageSize);
        }

        public DataTable ExecuteDataTable(string commandText, bool isProcedure, int pageIndex, int pageSize)
        {
            cmd.CommandType = (isProcedure ? CommandType.StoredProcedure : CommandType.Text);
            cmd.CommandText = commandText;

            DbDataAdapter da = GetDataAdapter();
            da.SelectCommand = cmd;

            try
            {
                if (pageIndex < 0)
                {
                    pageIndex = 0;
                }
                if (pageSize < 0)
                {
                    pageSize = 0;
                }

                DataTable dt = new DataTable();
                OpenConnection();
                if (pageSize > 0)
                {
                    da.Fill(pageIndex * pageSize, pageSize, dt);
                }
                else
                {
                    da.Fill(dt);
                }

                return dt;
            }
            catch (Exception er)
            {
                CloseConnection();
                throw er;
            }
            finally
            {
                if (!transactionSupport)
                {
                    CloseConnection();
                }

            }

        }


        #endregion

        #region Properties
        /// <summary>
        /// current connection
        /// </summary>
        public IDbConnection Connection
        {
            get { return conn; }
        }


        /// <summary>
        /// return sql command parameters
        /// when batch update, then the parameter can be used many times.
        /// </summary>
        public IDataParameterCollection Parameters
        {
            get
            {
                return this.cmd.Parameters;
            }
        }
        #endregion

        #region IDisposable Members
        /// <summary>
        /// Dispose resource
        /// </summary>
        public void Dispose()
        {
            if (cmd != null) cmd.Dispose();
            if (conn != null) conn.Dispose();
        }

        #endregion

        #endregion














    }
}
